# -*- coding: utf-8 -*-
'''
Created on 2013-4-11

@author: cl.lam
'''
import os
from datetime import datetime as dt
import random
import traceback

from flask import current_app as app
from flask import g, render_template, flash, redirect, url_for
from flask.blueprints import Blueprint
from flask.helpers import send_file
from sys2do.util.common import _g
from sqlalchemy.sql.expression import and_, or_
from openpyxl.reader.excel import load_workbook
from openpyxl.cell import get_column_letter
from openpyxl.style import Color, Fill, Border, Alignment
from openpyxl.workbook import Workbook


from sys2do.views import BasicView
from sys2do.util.decorator import templated, loginRequired, activetab
from sys2do.setting import TEMPLATE_FOLDER, TMP_FOLDER
from sys2do.constant import TAB_REPORT, MSG_NO_SUCH_ACTION, MESSAGE_ERROR, \
    ACTIVE, SYSTEM_DATE_FORMAT, SYSTEM_DATETIME_FORMAT, MSG_SERVER_ERROR, \
    IVTNT_IN, IVTNT_APPROVE, IVTNT_DISAPPROVE, FIN_APPROVE, FIN_REJECT
from sys2do.model import qry, PO, PODtl, User, Supplier, Product, ProductType, \
                InventoryProduct, Shop, InventoryLocation

from sys2do.util.logic_helper import getAllPermission, getCurrentShopProfile, \
    guessNo
from sys2do.model.logic import InventoryNote, InventoryNoteDtl, PODtl, FinNote, \
    FinNoteDtl
from sys2do.util.filters import ss






__all__ = ['bpRpt']

bpRpt = Blueprint( 'bpRpt', __name__ )

class RptView( BasicView ):

    template_folder = "rpt"

    @templated( "index.html" )
    @activetab( TAB_REPORT )
    @loginRequired
    def index( self ):
        return {}


    @templated( "form.html" )
    def form( self ):
        t = _g( 't' )
        if t not in ['sale', 'prh', 'ivt', 'ivtnt', 'fin' ] :
            flash( MSG_NO_SUCH_ACTION, MESSAGE_ERROR )
            return redirect( url_for( ".view" ) )

        shops = []
        if getAllPermission( ['SHOP_VIEW_ALL', ] ):
            shops = qry( Shop ).filter( and_( Shop.active == ACTIVE ) ).order_by( Shop.name ).all()

        values = {'t' : t, 'label' : None, 'shops' : shops}


        if t == 'sale':
            values['label'] = u'销售报表'
        elif t == 'prh':
            values['label'] = u'采购报表'
        elif t == 'ivt':
            values['label'] = u'仓存报表'
        elif t == 'ivtnt':
            values['label'] = u'出入库报表'
        elif t == 'fin':
            values['label'] = u'财务报表'

        return values


    def export( self ):
        t = _g( 't' )
        if t not in ['sale', 'prh', 'ivt', 'ivtnt', 'fin' ] :
            flash( MSG_NO_SUCH_ACTION, MESSAGE_ERROR )
            return redirect( url_for( ".view" ) )

        try:

            if t == 'sale':
                pass
            elif t == 'prh':
                return self._expPrh()
            elif t == 'ivt':
                return self._expIvt()
            elif t == 'ivtnt':
                return self._expIvtnt()
            elif t == 'fin':
                return self._expFin()
        except:
            self._error( traceback.format_exc() )
            flash( MSG_SERVER_ERROR, MESSAGE_ERROR )
            return redirect( url_for( '.view', action = 'form', t = t ) )



    def _expPrh( self ):
        cfrom = '%s 00:00:00' % _g( 'createTimeFrom', '2013-01-01' )
        cto = '%s 23:59:59' % _g( 'createTimeTo', dt.now().strftime( SYSTEM_DATE_FORMAT ) )

        cs = [ PO.active == ACTIVE, PODtl.active == ACTIVE,
               PO.createTime > cfrom, PO.createTime < cto,
               PO.shopID == Shop.id,
               PO.createById == User.id,
               PO.supplierID == Supplier.id,
               PO.id == PODtl.hdrID, PODtl.active == ACTIVE,
               PODtl.pdtID == Product.id,
               Product.typeID == ProductType.id,
              ]
        if getAllPermission( ['SHOP_VIEW_ALL', ] ):
            if _g( 'shopID' ):
                cs.append( Shop.id == _g( 'shopID' ) )
        else:
            spobj = getCurrentShopProfile()
            cs.append( Shop.id == spobj.shopID )

        result = qry( PO, Shop, Supplier, User, PODtl, Product, ProductType ).filter( and_( *cs ) ).order_by( Shop.name, PO.no, PODtl.id )
        data = [[u'店铺', u'采购订单', u'创建时间', u'经办人', u'供应商', u'联系人', u'联系电话',
                u'商品编号', u'商品名称', u'商品类型', u'数量', u'单价', u'总额', u'备注'], ]
        for p, sh, su, u, dtl, pdt, t in result :
            data.append( map( unicode, [sh, p.no, p.createTime.strftime( SYSTEM_DATE_FORMAT ), u, su,
                          p.att, p.tel or p.mobile or '', pdt.no, pdt.name,
                          t, dtl.qty, dtl.price, dtl.qty * dtl.price, dtl.remark or '' ] ) )

#         if not os.path.exists( TEMPLATE_FOLDER ) : os.makedirs( TEMPLATE_FOLDER )
#         templatePath = os.path.join( TEMPLATE_FOLDER, 'prh.xlsx' )
        if not os.path.exists( TMP_FOLDER ) : os.makedirs( TMP_FOLDER )
        fileName = 'purchase_%s%s.xlsx' % ( dt.now().strftime( "%Y%m%d%H%M" ), random.randint( 100, 999 ) )
        filePath = os.path.join( TMP_FOLDER, fileName )

#         wb = load_workbook( templatePath )
#         ws = wb.get_active_sheet()

        wb = Workbook()
        ws = wb.create_sheet( 0 )

        row, col, hrow = 1, 1, 1
        _hdr = ws.range( '%s%s:%s%s' % ( get_column_letter( col ), hrow, get_column_letter( col + len( data[0] ) - 1 ), hrow ) )
        for _row in _hdr :
            for _cell in _row:
                _cell.style.font.color.index = Color.WHITE
                _cell.style.font.name = 'Arial'
                _cell.style.font.bold = True
                _cell.style.font.size = 14

                _cell.style.borders.right.border_style = Border.BORDER_THIN
                _cell.style.borders.top.border_style = Border.BORDER_THIN
                _cell.style.borders.bottom.border_style = Border.BORDER_THIN

                _cell.style.fill.fill_type = Fill.FILL_SOLID
                _cell.style.fill.start_color.index = Color.DARKGREEN
                _cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
                _cell.style.alignment.vertical = Alignment.VERTICAL_CENTER
#                 _cell.style.alignment.wrap_text = True

        for d in data :
            for index, c in enumerate( d ):
                _cell = ws.cell( "%s%s" % ( get_column_letter( col + index ), row ) )
                _cell.set_value_explicit( c )
            row += 1

        for c in ['B', 'C', 'H', 'L', 'M']:        ws.column_dimensions[c].width = 20    # set the no column's width

        wb.save( filePath )
        return send_file( filePath, as_attachment = True, attachment_filename = fileName )



    def _expIvt( self ):
        cfrom = '%s 00:00:00' % _g( 'createTimeFrom', '2013-01-01' )
        cto = '%s 23:59:59' % _g( 'createTimeTo', dt.now().strftime( SYSTEM_DATE_FORMAT ) )

        cs = [
              InventoryProduct.ivtID == InventoryLocation.id,
              InventoryProduct.pdtID == Product.id,
              Product.typeID == ProductType.id,
              InventoryLocation.referID == Shop.id,
              InventoryProduct.qty > 0,
              ]
        if getAllPermission( ['SHOP_VIEW_ALL', ] ):
            if _g( 'shopID' ):
                cs.append( Shop.id == _g( 'shopID' ) )
        else:
            spobj = getCurrentShopProfile()
            cs.append( Shop.id == spobj.shopID )

        result = qry( Shop, InventoryLocation, Product,
                     ProductType, InventoryProduct ).filter( and_( * cs ) ).order_by( Shop.name,
                                                                            InventoryLocation.fullPath,
                                                                            Product.name )

        data = [[u'店铺', u'仓位', u'商品编号', u'商品名称', u'商品类型', u'数量', ], ]
        for sh, ivt, pdt, t, ip in result :
            data.append( map( unicode, [sh, ivt, pdt.no, pdt.name, t, ip.qty] ) )

        if not os.path.exists( TMP_FOLDER ) : os.makedirs( TMP_FOLDER )
        fileName = 'stock_%s%s.xlsx' % ( dt.now().strftime( "%Y%m%d%H%M" ), random.randint( 100, 999 ) )
        filePath = os.path.join( TMP_FOLDER, fileName )

        wb = Workbook()
        ws = wb.create_sheet( 0 )

        row, col, hrow = 1, 1, 1
        _hdr = ws.range( '%s%s:%s%s' % ( get_column_letter( col ), hrow, get_column_letter( col + len( data[0] ) - 1 ), hrow ) )
        for _row in _hdr :
            for _cell in _row:
                _cell.style.font.color.index = Color.WHITE
                _cell.style.font.name = 'Arial'
                _cell.style.font.bold = True
                _cell.style.font.size = 14

                _cell.style.borders.right.border_style = Border.BORDER_THIN
                _cell.style.borders.top.border_style = Border.BORDER_THIN
                _cell.style.borders.bottom.border_style = Border.BORDER_THIN

                _cell.style.fill.fill_type = Fill.FILL_SOLID
                _cell.style.fill.start_color.index = Color.DARKGREEN
                _cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
                _cell.style.alignment.vertical = Alignment.VERTICAL_CENTER
#                 _cell.style.alignment.wrap_text = True

        for d in data :
            for index, c in enumerate( d ):
                _cell = ws.cell( "%s%s" % ( get_column_letter( col + index ), row ) )
                _cell.set_value_explicit( c )
            row += 1

        for c in ['B', 'C', 'D', ]:        ws.column_dimensions[c].width = 30    # set the no column's width

        wb.save( filePath )
        return send_file( filePath, as_attachment = True, attachment_filename = fileName )



    def _expIvtnt( self ):
        cfrom = '%s 00:00:00' % _g( 'createTimeFrom', '2013-01-01' )
        cto = '%s 23:59:59' % _g( 'createTimeTo', dt.now().strftime( SYSTEM_DATE_FORMAT ) )

        cs = [
              InventoryNote.active == ACTIVE, InventoryNoteDtl.active == ACTIVE,
              InventoryNote.createTime > cfrom, InventoryNote.createTime < cto,
              InventoryNote.shopID == Shop.id, InventoryNote.createById == User.id,
              or_( InventoryNote.status == IVTNT_APPROVE, InventoryNote.status == IVTNT_DISAPPROVE ),
              InventoryNoteDtl.pdtID == Product.id,
              Product.typeID == ProductType.id,
              ]

        if getAllPermission( ['SHOP_VIEW_ALL', ] ):
            if _g( 'shopID' ):
                cs.append( Shop.id == _g( 'shopID' ) )
        else:
            spobj = getCurrentShopProfile()
            cs.append( Shop.id == spobj.shopID )

        ivtmapping = {}
        for ivt in qry( InventoryLocation ).all() : ivtmapping[ivt.id] = ivt.fullPath

        result = qry( Shop, User, InventoryNote, InventoryNoteDtl, Product, ProductType ).filter( and_( *cs ) ).order_by( Shop.name, InventoryNote.no )

        data = [[u'店铺', u'出入库单号', u'出/入', u'创建时间', u'审批时间', u'经办人', u'出入库原因', u'涉及', u'状态',
                 u'商品编号', u'商品名称', u'商品类型', u'源仓位', u'目的仓位', u'数量', u'备注' ], ]
        for sh, u, hdr, dtl, pdt, t in result :
            data.append( map( unicode, [sh, hdr.no, u'入库' if hdr.direction == IVTNT_IN else u'出库', hdr.createTime.strftime( SYSTEM_DATE_FORMAT ),
                   hdr.appTime.strftime( SYSTEM_DATE_FORMAT ) if hdr.appTime else '',
                   u, guessNo( hdr.refer ) if hdr.refer else '', hdr.refer or '', ss( hdr.status, 'IVTNT' ),
                   pdt.no, pdt.name, t, ivtmapping.get( dtl.sIvtLtnID, '' ),
                   ivtmapping.get( dtl.dIvtLtnID, '' ), dtl.qty, dtl.remark or '',
                   ] ) )

        if not os.path.exists( TMP_FOLDER ) : os.makedirs( TMP_FOLDER )
        fileName = 'inventory_%s%s.xlsx' % ( dt.now().strftime( "%Y%m%d%H%M" ), random.randint( 100, 999 ) )
        filePath = os.path.join( TMP_FOLDER, fileName )

        wb = Workbook()
        ws = wb.create_sheet( 0 )

        row, col, hrow = 1, 1, 1
        _hdr = ws.range( '%s%s:%s%s' % ( get_column_letter( col ), hrow, get_column_letter( col + len( data[0] ) - 1 ), hrow ) )
        for _row in _hdr :
            for _cell in _row:
                _cell.style.font.color.index = Color.WHITE
                _cell.style.font.name = 'Arial'
                _cell.style.font.bold = True
                _cell.style.font.size = 14

                _cell.style.borders.right.border_style = Border.BORDER_THIN
                _cell.style.borders.top.border_style = Border.BORDER_THIN
                _cell.style.borders.bottom.border_style = Border.BORDER_THIN

                _cell.style.fill.fill_type = Fill.FILL_SOLID
                _cell.style.fill.start_color.index = Color.DARKGREEN
                _cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
                _cell.style.alignment.vertical = Alignment.VERTICAL_CENTER
#                 _cell.style.alignment.wrap_text = True

        for d in data :
            for index, c in enumerate( d ):
                _cell = ws.cell( "%s%s" % ( get_column_letter( col + index ), row ) )
                _cell.set_value_explicit( c )
            row += 1

        for c in ['B', 'H', 'J', 'M', 'N', 'P']:        ws.column_dimensions[c].width = 25    # set the no column's width
        for c in ['A', 'D', 'E', 'G', 'L', 'K', ]:        ws.column_dimensions[c].width = 15    # set the no column's width

        wb.save( filePath )
        return send_file( filePath, as_attachment = True, attachment_filename = fileName )



    def _expFin( self ):
        cfrom = '%s 00:00:00' % _g( 'createTimeFrom', '2013-01-01' )
        cto = '%s 23:59:59' % _g( 'createTimeTo', dt.now().strftime( SYSTEM_DATE_FORMAT ) )

        cs = [
              FinNote.active == ACTIVE, FinNoteDtl.active == ACTIVE, FinNote.id == FinNoteDtl.hdrID,
              InventoryNote.createTime > cfrom, InventoryNote.createTime < cto,
              or_( FinNote.status == FIN_APPROVE, FinNote.status == FIN_REJECT ),
              FinNote.shopID == Shop.id, FinNote.createById == User.id,
              ]
        if getAllPermission( ['SHOP_VIEW_ALL', ] ):
            if _g( 'shopID' ):
                cs.append( Shop.id == _g( 'shopID' ) )
        else:
            spobj = getCurrentShopProfile()
            cs.append( Shop.id == spobj.shopID )

        data = [[u'店铺', u'账单编号', u'应付/应收', u'创建日期', u'经办人',
                 u'状态', u'涉及', u'子项目名称', u'子项目描述', u'数量', u'单价', u'金额', u'备注', ], ]
        result = qry( Shop, User, FinNote, FinNoteDtl ).filter( and_( *cs ) ).order_by( Shop.name, FinNote.no, FinNoteDtl.id )
        for sh, u, hdr, dtl in result :
            data.append( map( unicode, [sh, hdr.no, ss( hdr.direction, 'FIN' ), hdr.createTime.strftime( SYSTEM_DATE_FORMAT ),
                                        u, ss( hdr.status, 'FIN' ), hdr.refer, dtl.itemName or '', dtl.desc or '',
                                        dtl.qty, dtl.price, dtl.amount, dtl.remark or ''
                                        ] ) )

        if not os.path.exists( TMP_FOLDER ) : os.makedirs( TMP_FOLDER )
        fileName = 'inventory_%s%s.xlsx' % ( dt.now().strftime( "%Y%m%d%H%M" ), random.randint( 100, 999 ) )
        filePath = os.path.join( TMP_FOLDER, fileName )

        wb = Workbook()
        ws = wb.create_sheet( 0 )

        row, col, hrow = 1, 1, 1
        _hdr = ws.range( '%s%s:%s%s' % ( get_column_letter( col ), hrow, get_column_letter( col + len( data[0] ) - 1 ), hrow ) )
        for _row in _hdr :
            for _cell in _row:
                _cell.style.font.color.index = Color.WHITE
                _cell.style.font.name = 'Arial'
                _cell.style.font.bold = True
                _cell.style.font.size = 14

                _cell.style.borders.right.border_style = Border.BORDER_THIN
                _cell.style.borders.top.border_style = Border.BORDER_THIN
                _cell.style.borders.bottom.border_style = Border.BORDER_THIN

                _cell.style.fill.fill_type = Fill.FILL_SOLID
                _cell.style.fill.start_color.index = Color.DARKGREEN
                _cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
                _cell.style.alignment.vertical = Alignment.VERTICAL_CENTER
#                 _cell.style.alignment.wrap_text = True

        for d in data :
            for index, c in enumerate( d ):
                _cell = ws.cell( "%s%s" % ( get_column_letter( col + index ), row ) )
                _cell.set_value_explicit( c )
            row += 1

        for c in ['B', 'G', 'H', 'I', 'M']:        ws.column_dimensions[c].width = 25    # set the no column's width
        for c in ['D', 'E', 'L', 'K', ]:        ws.column_dimensions[c].width = 15    # set the no column's width

        wb.save( filePath )
        return send_file( filePath, as_attachment = True, attachment_filename = fileName )



bpRpt.add_url_rule( '/', view_func = RptView.as_view( 'view' ), defaults = {'action':'index'} )
bpRpt.add_url_rule( '/<action>', view_func = RptView.as_view( 'view' ) )
